package guoanmaker.provider.business.dao;

import java.math.BigDecimal;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.springframework.stereotype.Repository;

import guoanmaker.provider.business.model.ProvideOrderForm;
import guoanmaker.provider.business.modelnobind.ProvideOrderFormForOrder;
import guoanmaker.provider.business.modelnobind.UnreadOrderNum;
import guoanmaker.provider.helper.common.util.SystemHelper;

@Repository
public class ProvideOrderFormDao {

	@PersistenceContext
	EntityManager em;

	/**
	 * 
	* @Description: jpql查询订单,返回需要数据 
	* @param orderStatus 订单状态1待支付  2待接单  3服务中  4待确认   5退款处理中   6已完成   7已关闭
	* @param placeOrderTimeBegin 最早下单时间
	* @param placeOrderTimeEnd 最晚下单时间
	* @param priceBegin  最低价
	* @param priceEnd   最高价
	* @param commodityNameOrUserPhone  商品名称或者用户手机号
	* @param pageNumber  分页页数 ,从0开始
	* @param pageSize  分页每业条数
	* @param @return
	* @author Yvonne_MGL 
	* @date 2017年7月11日 下午8:33:13     
	* @return List<ProvideOrderFormForOrder>    
	* @throws 
	 */
	public Map findByProperties(String orderStatus, String placeOrderTimeBegin,
			String placeOrderTimeEnd, Double priceBegin, Double priceEnd, String commodityNameOrUserPhone,
			String businessid, Integer pageNumber, Integer pageSize) {
		String jpql = "select new guoanmaker.provider.business.modelnobind.ProvideOrderFormForOrder"
				+ "(o.id,o.orderNumber,o.placeOrderTime,o.quantity,o.unitPrice,o.truePrice,o.orderStatus,o.remark,o.defrayTime,"
				+ "o.acceptOrderTime,o.defrayNumber,o.applyforCompleteTime,o.confirmTime,o.applyforRefundsTime,"
				+ "o.confirmRefundsTime,o.denyRefundsTime,o.denyRefundsReason,o.contactPerson,o.contactNumber,"
				+ "o.requirementDescription,o.isOverChangePrice,o.orderType,o.userCompany,o.visitTime,o.visitorsNum,"
				+ "o.rentedBeginDate,o.rentedEndDate,o.rentedNum,o.commission,o.bail,o.fee,u.username,u.nickname,u.email,"
				+ "u.remark,c.commodityName,t.serverName,o.closeTime,o.isRead,c.invoiceMail,o.invoiceState,o.invoiceid,o.isCheck,"
				+ "c.commodity_smallimage,o.closeType,o.serverPlace,o.preferentialMoney,o.refundsAmount,o.refundsReason) from ProvideOrderForm o left join o.user u left join o.commodity c "
				+ "left join c.providerBusiness b left join c.serviceType t where 1=1 ";
		if(orderStatus!=null&&!orderStatus.equals("")){
			if(orderStatus.equals("5")){
				jpql+="and (o.orderStatus='5' or o.orderStatus='9' or o.orderStatus='10')";
			}else if(orderStatus.equals("7")){
				jpql+="and (o.orderStatus='7' or o.orderStatus='8' or o.orderStatus='11' or o.orderStatus='12' or o.orderStatus='15')";
			}else if(orderStatus.equals("6")){
				jpql+="and (o.orderStatus='6' or o.orderStatus='13' )";
			}
			else{
				jpql+="and o.orderStatus='"+orderStatus+"'";
			}
			
		}
		if(placeOrderTimeBegin!=null&&!placeOrderTimeBegin.equals("")){
			jpql+="and o.placeOrderTime>='"+placeOrderTimeBegin+"'";
		}
		if(placeOrderTimeEnd!=null&&!placeOrderTimeEnd.equals("")){
			jpql+="and o.placeOrderTime<='"+placeOrderTimeEnd+"'";
		}
		if(priceBegin!=null){
			jpql+="and o.truePrice>="+priceBegin;
		}
		if(priceEnd!=null){
			jpql+="and o.truePrice<="+priceEnd;
		}
		if(commodityNameOrUserPhone!=null&&!commodityNameOrUserPhone.equals("")){
			if(SystemHelper.isNumeric(commodityNameOrUserPhone)){
				jpql+="and u.username='"+commodityNameOrUserPhone+"'";
			}else{
				jpql+="and c.commodityName like '%"+commodityNameOrUserPhone+"%'";
			}
		}
		if(businessid!=null&&!businessid.equals("")){
			jpql+="and b.id='"+businessid+"'";
		}
		jpql+=" order by o.placeOrderTime desc";
		if(pageNumber==null){
			pageNumber=0;
		}
		if (pageSize==null) {
			pageSize=10;
		}
		Query query = em.createQuery(jpql);
		List<ProvideOrderFormForOrder> list = query.getResultList();
		Map map=new HashMap();
		map.put("totle", list.size());
		query.setFirstResult(pageNumber*pageSize); // 序号从0 开始
		query.setMaxResults(pageSize);

		List<ProvideOrderFormForOrder> result = query.getResultList();
		map.put("result", result);
		
		
		return map;
	}
	
	/**
	 * 
	* @Description: 根据id获取订单详情 
	* @param id 订单id
	* @param @return
	* @author Yvonne_MGL 
	* @date 2017年7月12日 上午11:33:25     
	* @return ProvideOrderFormForOrder    
	* @throws 
	 */
	public ProvideOrderFormForOrder findByid(String id){
		ProvideOrderFormForOrder order=new ProvideOrderFormForOrder();
		String jpql = "select new guoanmaker.provider.business.modelnobind.ProvideOrderFormForOrder"
				+ "(o.id,o.orderNumber,o.placeOrderTime,o.quantity,o.unitPrice,o.truePrice,o.orderStatus,o.remark,o.defrayTime,"
				+ "o.acceptOrderTime,o.defrayNumber,o.applyforCompleteTime,o.confirmTime,o.applyforRefundsTime,"
				+ "o.confirmRefundsTime,o.denyRefundsTime,o.denyRefundsReason,o.contactPerson,o.contactNumber,"
				+ "o.requirementDescription,o.isOverChangePrice,o.orderType,o.userCompany,o.visitTime,o.visitorsNum,"
				+ "o.rentedBeginDate,o.rentedEndDate,o.rentedNum,o.commission,o.bail,o.fee,u.username,u.nickname,u.email,"
				+ "u.remark,c.commodityName,t.serverName,o.closeTime,o.isRead,c.invoiceMail,o.invoiceState,o.invoiceid,o.isCheck,"
				+ "c.commodity_smallimage,o.closeType,o.serverPlace,o.preferentialMoney,o.refundsAmount,o.refundsReason) from ProvideOrderForm o left join o.user u left join o.commodity c "
				+ "left join c.serviceType t where o.id='"+id+"'";
		Query query = em.createQuery(jpql);
		List<ProvideOrderFormForOrder> result = query.getResultList();
		if(result!=null&&result.size()>0){
			order=result.get(0);
		}
		return order;
	}
	
	/**
	 * 
	* @Description: 查询未读订单数量
	* @param businessid 服务商id
	* @param @return
	* @author Yvonne_MGL 
	* @date 2017年7月21日 上午10:58:30     
	* @return UnreadOrderNum    
	* @throws 
	 */
	public UnreadOrderNum findUnread(String businessid){
		UnreadOrderNum unreadOrderNum=new UnreadOrderNum();
		String jpqltoBePaid=     "select o from ProvideOrderForm o join o.commodity c join c.providerBusiness b where o.orderStatus='1' and o.isRead='0' and b.id='"+businessid+"'";
		String jpqltoBeOrders=   "select o from ProvideOrderForm o join o.commodity c join c.providerBusiness b where o.orderStatus='2' and o.isRead='0' and b.id='"+businessid+"'";
		String jpqltoBeConfirmed="select o from ProvideOrderForm o join o.commodity c join c.providerBusiness b where o.orderStatus='4' and o.isRead='0' and b.id='"+businessid+"'";
		String jpqlrefunding=    "select o from ProvideOrderForm o join o.commodity c join c.providerBusiness b where (o.orderStatus='5' or o.orderStatus='9' or o.orderStatus='10' ) and o.isRead='0' and b.id='"+businessid+"'";
		String jpqlcompleted=    "select o from ProvideOrderForm o join o.commodity c join c.providerBusiness b where (o.orderStatus='6' or o.orderStatus='13' ) and o.isRead='0' and b.id='"+businessid+"'";
		String jpqlclosed=       "select o from ProvideOrderForm o join o.commodity c join c.providerBusiness b where (o.orderStatus='7' or o.orderStatus='11' or o.orderStatus='12' ) and o.isRead='0' and b.id='"+businessid+"'";
		String jpqltoBeInterflow="select o from ProvideOrderForm o join o.commodity c join c.providerBusiness b where o.orderStatus='14' and o.isRead='0' and b.id='"+businessid+"'";
		
		Query querytoBePaid = em.createQuery(jpqltoBePaid);
		List<ProvideOrderForm> listtoBePaid=querytoBePaid.getResultList();
		unreadOrderNum.setToBePaid(listtoBePaid.size());
		
		Query querytoBeOrders = em.createQuery(jpqltoBeOrders);
		List<ProvideOrderForm> listtoBeOrders=querytoBeOrders.getResultList();
		unreadOrderNum.setToBeOrders(listtoBeOrders.size());
		
		Query querytoBeConfirmed = em.createQuery(jpqltoBeConfirmed);
		List<ProvideOrderForm> listtoBeConfirmed=querytoBeConfirmed.getResultList();
		unreadOrderNum.setToBeConfirmed(listtoBeConfirmed.size());
		
		Query queryrefunding = em.createQuery(jpqlrefunding);
		List<ProvideOrderForm> listrefunding=queryrefunding.getResultList();
		unreadOrderNum.setRefunding(listrefunding.size());
		
		Query querycompleted = em.createQuery(jpqlcompleted);
		List<ProvideOrderForm> listcompleted=querycompleted.getResultList();
		unreadOrderNum.setCompleted(listcompleted.size());
		
		Query queryclosed = em.createQuery(jpqlclosed);
		List<ProvideOrderForm> listclosed=queryclosed.getResultList();
		unreadOrderNum.setClosed(listclosed.size());
		
		Query querytoBeInterflow = em.createQuery(jpqltoBeInterflow);
		List<ProvideOrderForm> listtoBeInterflow=querytoBeInterflow.getResultList();
		unreadOrderNum.setToBeInterflow(listtoBeInterflow.size());
		return unreadOrderNum;
	}
	
	/**
	 * 
	* @Description: 查询订单发票情况 
	* @param invoiceState 开发票状态
	* @param priceBegin 起始价格
	* @param priceEnd 结束价格
	* @param orderNumber 订单编号
	* @param commodityName 商品名称
	* @param pageNumber  分页页数 ,从0开始
	* @param pageSize  分页每业条数
	* @param @return
	* @author Yvonne_MGL 
	* @date 2017年7月27日 下午4:55:51     
	* @return Map    
	* @throws 
	 */
	public Map findInvoiceOrderForm(String invoiceState, Double priceBegin, Double priceEnd,String orderNumber,String commodityName, Integer pageNumber, Integer pageSize,String businessid){
		String jpql = "select new guoanmaker.provider.business.modelnobind.ProvideOrderFormForOrder"
				+ "(o.id,o.orderNumber,o.placeOrderTime,o.quantity,o.unitPrice,o.truePrice,o.orderStatus,o.remark,o.defrayTime,"
				+ "o.acceptOrderTime,o.defrayNumber,o.applyforCompleteTime,o.confirmTime,o.applyforRefundsTime,"
				+ "o.confirmRefundsTime,o.denyRefundsTime,o.denyRefundsReason,o.contactPerson,o.contactNumber,"
				+ "o.requirementDescription,o.isOverChangePrice,o.orderType,o.userCompany,o.visitTime,o.visitorsNum,"
				+ "o.rentedBeginDate,o.rentedEndDate,o.rentedNum,o.commission,o.bail,o.fee,u.username,u.nickname,u.email,"
				+ "u.remark,c.commodityName,t.serverName,o.closeTime,o.isRead,c.invoiceMail,o.invoiceState,o.invoiceid,o.isCheck,"
				+ "c.commodity_smallimage,o.closeType,o.serverPlace,o.preferentialMoney,o.refundsAmount,o.refundsReason) from ProvideOrderForm o left join o.user u left join o.commodity c "
				+ "left join c.providerBusiness b left join c.serviceType t where 1=1 and o.invoiceState!=null and o.invoiceState!=''  and o.invoiceState is not null ";
		if(invoiceState!=null&&!invoiceState.equals("")){
			jpql+=" and o.invoiceState='"+invoiceState+"'";
		}
		if(priceBegin!=null){
			jpql+=" and o.truePrice>="+priceBegin;
		}
		if(priceEnd!=null){
			jpql+=" and o.truePrice<="+priceEnd;
		}
		if(commodityName!=null&&!commodityName.equals("")){
			jpql+=" and c.commodityName like '%"+commodityName+"%'";
		}
		if(orderNumber!=null&&!orderNumber.equals("")){
			jpql+=" and c.orderNumber='"+orderNumber+"'";
		}
		if(businessid!=null&&!businessid.equals("")){
			jpql+=" and b.id='"+businessid+"'";
		}
		jpql+=" order by o.placeOrderTime desc";
		if(pageNumber==null){
			pageNumber=0;
		}
		if (pageSize==null) {
			pageSize=10;
		}
		Query query = em.createQuery(jpql);
		List<ProvideOrderFormForOrder> list = query.getResultList();
		Map map=new HashMap();
		map.put("totle", list.size());
		query.setFirstResult(pageNumber*pageSize); // 序号从0 开始
		query.setMaxResults(pageSize);

		List<ProvideOrderFormForOrder> result = query.getResultList();
		map.put("result", result);
		
		return map;
	}
	
	/**
	 * 
	* @Description: 根据服务商id查询服务商一共交过的保证金金额
	* @param businessid 服务商id
	* @param @return
	* @author Yvonne_MGL 
	* @date 2017年8月10日 下午2:24:55     
	* @return int    
	* @throws 
	 */
	public int getSumBail(String businessid) {
		String jpql = "select sum(o.bail) from ProvideOrderForm o join o.commodity c join c.providerBusiness b where b.id='"
				+ businessid + "'";
		Query query = em.createQuery(jpql);
		List list = query.getResultList();
		BigDecimal sumBail = (BigDecimal) list.get(0);
		if (sumBail == null) {
			sumBail = new BigDecimal(0.00);
		}
		int result = Integer.parseInt(
				(sumBail.doubleValue() * 100 + "").substring(0, (sumBail.doubleValue() * 100 + "").indexOf(".")));
		return result;
	}
	
	/**
	 * 
	* @Description: 查询未读订单 
	* @param businessid 服务商id
	* @param state 订单状态
	* @param @return
	* @author Yvonne_MGL 
	* @date 2017年9月13日 下午7:13:08     
	* @return List<ProvideOrderForm>    
	* @throws 
	 */
	public List<ProvideOrderForm> findUnReadOrders(String businessid,String state){
		String jpql="select o from ProvideOrderForm o join o.commodity c join c.providerBusiness b where o.isRead='0' ";
		if(businessid!=null&&!businessid.equals("")){
			jpql+=" and b.id='"+businessid+"'";
		}
		if(state!=null&&!state.equals("")){
			if(state.equals("5")){
				jpql+=" and (o.orderStatus='5' or o.orderStatus='9' or o.orderStatus='10')";
			}else if(state.equals("7")){
				jpql+=" and (o.orderStatus='7' or o.orderStatus='8' or o.orderStatus='11' or o.orderStatus='12' or o.orderStatus='15')";
			}else if(state.equals("6")){
				jpql+=" and (o.orderStatus='6' or o.orderStatus='13' )";
			}
			else{
				jpql+=" and o.orderStatus='"+state+"'";
			}
			
		}
		Query query = em.createQuery(jpql);
		List<ProvideOrderForm> list = query.getResultList();
		return list;
	}
	
	/**
	 * 
	* @Description: 根据服务商id查询服务商的佣金、手续费、保证金总额 
	* @param businessid 服务商id
	* @param @return
	* @author Yvonne_MGL 
	* @date 2017年11月14日 上午10:19:59     
	* @return Map    
	* @throws 
	 */
	public Map getStatistics(String businessid){
		Map map=new HashMap();
		String jpqlfee="select sum(o.fee) from ProvideOrderForm o join o.commodity c join c.providerBusiness b where b.id='"+businessid+"'";
		Query queryfee = em.createQuery(jpqlfee);
		List listfee=queryfee.getResultList();
		BigDecimal sumFee = (BigDecimal) listfee.get(0);
		if (sumFee == null) {
			sumFee = new BigDecimal(0.00);
		}
		map.put("sumFee", sumFee);
		
		String jpqlbail="select sum(o.bail) from ProvideOrderForm o join o.commodity c join c.providerBusiness b where b.id='"+businessid+"'";
		Query querybail = em.createQuery(jpqlbail);
		List listbail=querybail.getResultList();
		BigDecimal sumBail = (BigDecimal) listbail.get(0);
		if (sumBail == null) {
			sumBail = new BigDecimal(0.00);
		}
		map.put("sumBail", sumBail);
		
		String jpqlcommission="select sum(o.commission) from ProvideOrderForm o join o.commodity c join c.providerBusiness b where b.id='"+businessid+"'";
		Query querycommission = em.createQuery(jpqlcommission);
		List listcommission=querycommission.getResultList();
		BigDecimal sumCommission = (BigDecimal) listcommission.get(0);
		if (sumCommission == null) {
			sumCommission = new BigDecimal(0.00);
		}
		map.put("sumCommission", sumCommission);
		
		return map;
	}
	
	/**
	 * 
	* @Description: 初始化returnPreferentialMoney，查询发生退款的订单 
	* @param @return
	* @author Yvonne_MGL 
	* @date 2017年11月15日 上午9:56:07     
	* @return List<ProvideOrderForm>    
	* @throws 
	 */
	public List<ProvideOrderForm> getRefundsOrder(){
		String jpql="select o from ProvideOrderForm o where o.orderStatus in ('8','10','11','12')";
		Query query = em.createQuery(jpql);
		List<ProvideOrderForm> list = query.getResultList();
		return list;
		
	}
	
	/**
	 * 
	* @Description: 查询未对账的订单
	* @param @return
	* @author Yvonne_MGL 
	* @date 2017年11月15日 上午9:56:07     
	* @return List<ProvideOrderForm>    
	* @throws 
	 */
	public List<ProvideOrderForm> getNotCheckOrder(){
		String jpql="select o from ProvideOrderForm o where o.orderStatus in ('6','13','8','10','11','12') and (o.isCheck='0' or o.isCheck is null)";
		Query query = em.createQuery(jpql);
		List<ProvideOrderForm> list = query.getResultList();
		return list;
		
	}
	
	/**
	 * 
	* @Description: 查询超过10分钟未付款的秒杀订单 
	* @param @return
	* @author Yvonne_MGL 
	* @date 2017年11月29日 下午4:12:22     
	* @return List<ProvideOrderForm>    
	* @throws 
	 */
	public List<ProvideOrderForm> getOrder12(){
		String date=SystemHelper.getLess10Min();
		String jpql="select o from ProvideOrderForm o where o.orderStatus ='1' and o.spikeid is not null and o.spikeid!='' and o.placeOrderTime<='"+date+"' ";
		Query query = em.createQuery(jpql);
		List<ProvideOrderForm> list = query.getResultList();
		return list;
		
	}
	
	

}
